Is Col 1 Data In Col 2 and vice versa
In Excel, you can compare the cells in two ranges with an array formula.
Consider this table - look at columns 1 and 4 - note the occurrence of the values
Value |
Is A in D |
(BlankCell) |
Value |
Is D in A |
1 |
Yes |
|
0 |
No |
2 |
Yes |
|
1 |
Yes |
3 |
No |
|
2 |
Yes |
4 |
No |
|
6 |
No |
5 |
No |
|
7 |
No |
The table is obtained by :
Value |
Is A in D |
(BlankCell) |
Value |
Is D in A |
1 |
=IF(ISNA(VLOOKUP(A2,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
0 |
=IF(ISNA(VLOOKUP(D2,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
2 |
=IF(ISNA(VLOOKUP(A3,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
1 |
=IF(ISNA(VLOOKUP(D3,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
3 |
=IF(ISNA(VLOOKUP(A4,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
2 |
=IF(ISNA(VLOOKUP(D4,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
4 |
=IF(ISNA(VLOOKUP(A5,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
6 |
=IF(ISNA(VLOOKUP(D5,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
5 |
=IF(ISNA(VLOOKUP(A6,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
7 |
=IF(ISNA(VLOOKUP(D6,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
|